Text Manipulation Using SUBSTRING, TRIM, and REPLACE in MySQL – Detailed Notes

Course: MySQL Training

Topic: Text Manipulation Using SUBSTRING, TRIM, and REPLACE

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Text Manipulation in MySQL

Text manipulation is the process of extracting, cleaning, modifying, or transforming string values stored in a database. In MySQL, text data is commonly stored in columns with data types such as CHAR, VARCHAR, and TEXT.

Real-world databases often contain names, email addresses, phone numbers, product descriptions, addresses, order references, and other text-based information. Sometimes these values contain unnecessary spaces, inconsistent wording, or additional characters that make them difficult to use in reports and applications.

MySQL provides built-in string functions to handle these situations. Three especially useful functions are:

• SUBSTRING(): Extracts a portion of a string from a specified position.
• TRIM(): Removes specified characters from the beginning or end of a string; by default, it removes spaces.
• REPLACE(): Replaces all occurrences of a specified substring with another substring.

These functions can be used in SELECT queries to transform displayed results, in filters and sorting expressions, and in data-modification statements when permanent changes are intended.

SELECT SUBSTRING('MySQL Training', 1, 5) AS extracted_text;
SELECT TRIM('   MySQL   ') AS cleaned_text;
SELECT REPLACE('Learn SQL', 'SQL', 'MySQL') AS updated_text;

These examples demonstrate the basic purpose of each function. The following sections explain their syntax, behavior, practical applications, common mistakes, and use in real database projects.

2. Learning Objectives

After completing this topic, you should be able to:

• Understand the role of string functions in MySQL.
• Extract characters from the beginning, middle, or end of a string using SUBSTRING().
• Understand how string positions and lengths work in MySQL.
• Remove unwanted leading and trailing spaces using TRIM().
• Use LEADING, TRAILING, and BOTH options with TRIM().
• Replace words, characters, and text patterns using REPLACE().
• Combine the three functions to clean and standardize text.
• Apply text manipulation to employee, customer, product, and order data.
• Use string functions with filtering, sorting, and data-modification queries.
• Identify common errors and understand performance considerations.

3. Understanding Strings in MySQL

A string is a sequence of characters. It can contain letters, digits, spaces, punctuation marks, and other characters. In MySQL, strings may be stored in columns such as VARCHAR and TEXT.

3.1 Example of Text Data

Consider a table named customers:

In this example, the first customer's name contains extra spaces before and after the name, while the second customer's city has trailing and leading spaces. Such inconsistencies can make exported data look untidy and may affect comparisons, matching, and reporting.

String functions help prepare these values for display or analysis. A function used in a SELECT query does not automatically update the original table data.

4. SUBSTRING() Function in MySQL

The SUBSTRING() function extracts a portion of a string based on a starting position and, optionally, a length. It is useful when only a specific part of a text value is required.

4.1 Syntax of SUBSTRING()

SUBSTRING(string, start_position)
SUBSTRING(string, start_position, length)

MySQL also supports the SQL-style syntax:

SUBSTRING(string FROM start_position)
SUBSTRING(string FROM start_position FOR length)

Here:

• string: The text value or column from which characters are extracted.
• start_position: The position where extraction begins.
• length: The optional number of characters to return.

4.2 Understanding String Positions

MySQL counts the first character of a string as position 1, not position 0.

For the string DATABASE, the positions are:

For example, starting at position 1 and extracting four characters returns DATA.

SELECT SUBSTRING('DATABASE', 1, 4) AS result;
-- Result: DATA

4.3 Extracting Text from the Beginning

To extract a fixed number of characters from the beginning of a string, start at position 1.

SELECT SUBSTRING('MySQL Training', 1, 5) AS result;
-- Result: MySQL

SELECT SUBSTRING('Customer12345', 1, 8) AS result;
-- Result: Customer

This technique is useful when working with fixed prefixes, product codes, short labels, or standardized identifiers.

4.4 Extracting Text from the Middle

To extract text from the middle of a string, specify the starting position and the number of characters required.

SELECT SUBSTRING('ABCDEFGHIJ', 4, 3) AS result;
-- Result: DEF

SELECT SUBSTRING('MySQL Training', 7, 8) AS result;
-- Result: Training

In the first query, extraction starts at position 4 and returns three characters. In the second query, extraction begins at position 7 and returns eight characters.

4.5 Extracting Text from a Specified Position to the End

If the length argument is omitted, MySQL returns the remaining portion of the string from the specified starting position.

SELECT SUBSTRING('MySQL Training', 7) AS result;
-- Result: Training

SELECT SUBSTRING('ORD-2026-125', 5) AS result;
-- Result: 2026-125

This is useful when a value contains a known prefix and the remaining text needs to be extracted.

4.6 Using Negative Positions

MySQL allows a negative starting position in SUBSTRING(). A negative position counts backward from the end of the string.

SELECT SUBSTRING('DATABASE', -4) AS result;
-- Result: BASE

SELECT SUBSTRING('DATABASE', -3, 2) AS result;
-- Result: AS

In the first example, the last four characters are returned. In the second, extraction starts three characters from the end and returns two characters.

4.7 What Happens with Position Zero?

In MySQL, a starting position of zero returns an empty string.

SELECT SUBSTRING('DATABASE', 0, 4) AS result;
-- Result: empty string

Remember that ordinary character positions start at 1. A zero position is generally not appropriate when extracting a normal substring.

4.8 What Happens When Length Is Less Than One?

If the requested length is zero or negative, SUBSTRING() returns an empty string.

SELECT SUBSTRING('MySQL', 2, 0) AS result;
-- Result: empty string

If the requested length extends beyond the end of the string, MySQL returns the characters that remain instead of producing an error.

SELECT SUBSTRING('MySQL', 4, 10) AS result;
-- Result: QL

4.9 Using SUBSTRING() with Table Columns

Functions become especially useful when applied to column values rather than fixed text.

SELECT
    customer_name,
    SUBSTRING(customer_name, 1, 4) AS short_name
FROM customers;

This query returns the original customer name along with its first four characters. The extracted value is calculated for the result and does not change the stored name.

4.10 Extracting Parts of Product Codes

Suppose a product code follows the format PRD-2026-450. If the year always begins at position 5 and has four characters, it can be extracted as follows:

SELECT
    product_code,
    SUBSTRING(product_code, 5, 4) AS product_year
FROM products;

This example relies on a consistent code format. If codes have different prefix lengths or separators, use a delimiter-aware method or validate the format before extracting positions.

5. TRIM() Function in MySQL

The TRIM() function removes specified characters from the beginning and/or end of a string. When no removal string is specified, it removes leading and trailing spaces.

It is commonly used to clean names, addresses, user input, imported spreadsheet data, and other text values.

5.1 Basic Syntax

TRIM(string)
TRIM([BOTH | LEADING | TRAILING] [removal_string] FROM string)

Here:

• string: The text value to be cleaned.
• BOTH: Removes the specified characters from both ends. This is the default behavior.
• LEADING: Removes the specified characters from the beginning only.
• TRAILING: Removes the specified characters from the end only.
• removal_string: The character sequence to remove. If omitted, spaces are removed.

5.2 Removing Leading and Trailing Spaces

Leading spaces appear before the meaningful text, while trailing spaces appear after it.

SELECT TRIM('   MySQL Training   ') AS result;
-- Result: MySQL Training

In this example, the spaces at both ends are removed, but the space between MySQL and Training remains.

5.3 Removing Leading Spaces with LEADING

Use TRIM(LEADING ... FROM ...) to remove characters only from the beginning of a string.

SELECT TRIM(LEADING FROM '   MySQL') AS result;
-- Result: MySQL

When the removal string is omitted, leading spaces are removed. Trailing spaces are not targeted by this form.

5.4 Removing Trailing Spaces with TRAILING

Use TRIM(TRAILING ... FROM ...) to remove characters only from the end of a string.

SELECT TRIM(TRAILING FROM 'MySQL   ') AS result;
-- Result: MySQL

This can be helpful when text imported from another system contains extra spaces at the end.

5.5 Removing Characters from Both Ends

The BOTH option removes the specified string from both ends of the input.

SELECT TRIM(BOTH 'x' FROM 'xxxMySQLxxx') AS result;
-- Result: MySQL

Here, the character x is removed from the beginning and end. Characters inside the word are not removed.

5.6 Removing a Prefix or Suffix

You can remove a specified character sequence from one end of a string.

SELECT TRIM(LEADING 'ID-' FROM 'ID-4587') AS result;
-- Result: 4587

SELECT TRIM(TRAILING '.com' FROM 'example.com') AS result;
-- Result: example

Use this form when the unwanted text occurs at the edge of the string. TRIM() is not a general-purpose replacement function for removing text from the middle.

5.7 Important Behavior of TRIM()

When a removal string is specified, MySQL removes repeated occurrences of that string from the designated end or ends. It does not treat the removal string as a set of independent characters.

SELECT TRIM(LEADING 'ab' FROM 'abababMySQL') AS result;
-- Result: MySQL

The repeated prefix ab is removed. The removal sequence must match at the edge of the string.

5.8 TRIM() Does Not Remove Internal Spaces

A common misunderstanding is assuming that TRIM() removes every space in a string. It removes the targeted characters only from the specified ends.

SELECT TRIM('   My SQL Training   ') AS result;
-- Result: My SQL Training

The space between My and SQL remains. If internal spaces need to be changed, use a suitable replacement operation, such as REPLACE(), with care.

5.9 Related Functions: LTRIM() and RTRIM()

MySQL also provides functions that remove spaces from one side.

SELECT LTRIM('   MySQL   ') AS result;
-- Result: MySQL   

SELECT RTRIM('   MySQL   ') AS result;
-- Result:    MySQL

LTRIM() removes leading spaces, while RTRIM() removes trailing spaces. Use them when only one side needs to be cleaned.

6. REPLACE() Function in MySQL

The REPLACE() function searches a string for occurrences of a specified substring and replaces every matching occurrence with another substring.

It is useful for correcting wording, standardizing text, updating separators in displayed values, and cleaning known unwanted text sequences.

6.1 Syntax of REPLACE()

REPLACE(original_string, search_string, replacement_string)

Here:

• original_string: The input text or column value.
• search_string: The substring that MySQL searches for.
• replacement_string: The text that replaces each match.

6.2 Replacing a Word

SELECT REPLACE('Learn SQL Basics', 'SQL', 'MySQL') AS result;
-- Result: Learn MySQL Basics

MySQL finds the substring SQL and replaces it with MySQL.

6.3 Replacing All Occurrences

REPLACE() replaces all occurrences of the search string in the input, not just the first occurrence.

SELECT REPLACE('red-red-red', 'red', 'blue') AS result;
-- Result: blue-blue-blue

6.4 Removing Characters Using REPLACE()

To remove a character or substring, use an empty string as the replacement value.

SELECT REPLACE('123-456-789', '-', '') AS result;
-- Result: 123456789

SELECT REPLACE('My SQL Training', ' ', '') AS result;
-- Result: MySQLTraining

In the second example, all ordinary space characters are removed, including the space between words. Use this technique only when removing internal spaces is appropriate.

6.5 Replacing Separators

Text imported from external systems may use inconsistent separators. REPLACE() can convert one separator to another.

SELECT REPLACE('2026/10/16', '/', '-') AS result;
-- Result: 2026-10-16

SELECT REPLACE('Mumbai|Pune|Delhi', '|', ', ') AS result;
-- Result: Mumbai, Pune, Delhi

Changing the visual format of a date-like string does not automatically convert it into a MySQL DATE value. Use date conversion functions when a true date value is needed.

6.6 Replacing Text in a Table Column

You can apply REPLACE() to a column in a SELECT query to show a transformed value.

SELECT
    product_name,
    REPLACE(product_name, 'Old', 'New') AS displayed_name
FROM products;

This query changes only the displayed expression. It does not modify the original product names stored in the table.

6.7 Case Sensitivity of REPLACE()

In MySQL, REPLACE() performs a case-sensitive search for the substring being replaced.

SELECT REPLACE('MySQL mysql MYSQL', 'mysql', 'DB') AS result;
-- Result: MySQL DB MYSQL

Only the lowercase mysql occurrence is replaced in this example. The uppercase and mixed-case forms remain unchanged.

6.8 What Happens When an Argument Is NULL?

If any argument to REPLACE() is NULL, the function returns NULL.

SELECT REPLACE(NULL, 'A', 'B') AS result;
-- Result: NULL

When working with table columns that may contain NULL values, consider whether NULL should remain NULL or be handled explicitly using a function such as COALESCE().

7. Difference Between SUBSTRING(), TRIM(), and REPLACE()

Although all three functions work with strings, each has a different purpose.

8. Combining SUBSTRING(), TRIM(), and REPLACE()

In real database tasks, a single function may not be sufficient. Functions can be nested to clean and transform text in stages.

8.1 Basic Combination

SELECT
    REPLACE(
        TRIM('   My SQL Training   '),
        ' ',
        '-'
    ) AS result;
-- Result: My-SQL-Training

First, TRIM() removes leading and trailing spaces. Next, REPLACE() changes the remaining spaces to hyphens.

8.2 Extracting a Cleaned Substring

SELECT
    SUBSTRING(
        TRIM('   Customer-4587   '),
        10,
        4
    ) AS result;

The result depends on the positions in the trimmed string. When extracting values from data with inconsistent formats, clean the input first and verify the starting position before relying on a fixed-length extraction.

8.3 Removing Spaces and Extracting a Code

SELECT
    SUBSTRING(
        REPLACE('PRD - 2026 - 450', ' ', ''),
        5,
        4
    ) AS product_year;
-- Result: 2026

The query removes ordinary spaces, then extracts four characters beginning at position 5. This approach is appropriate only if the cleaned product code follows the expected format.

8.4 Nesting Functions: Order of Evaluation

When functions are nested, the innermost function is evaluated first. Its result becomes the input for the function that surrounds it.

SELECT REPLACE(TRIM('  SQL Training  '), ' ', '_') AS result;
-- Result: SQL_Training

Evaluation order:

1. TRIM() removes the leading and trailing spaces.
2. REPLACE() changes the remaining space to an underscore.
3. MySQL returns the final calculated string.

9. Practical Examples Using Customer Data

Consider a table named customers with the following columns:

9.1 Remove Extra Edge Spaces from Names

SELECT
    customer_id,
    customer_name,
    TRIM(customer_name) AS cleaned_name
FROM customers;

9.2 Extract the First Five Characters of a Customer Code

SELECT
    customer_code,
    SUBSTRING(customer_code, 1, 5) AS code_prefix
FROM customers;

9.3 Remove Hyphens from Phone Numbers

SELECT
    phone_number,
    REPLACE(phone_number, '-', '') AS normalized_phone
FROM customers;

This example removes hyphens from the displayed value. It does not validate whether the resulting phone number is correct or complete. Phone numbers should generally be stored as text rather than numeric values because they may contain leading zeros, country codes, or formatting symbols.

9.4 Clean City Names

SELECT
    customer_id,
    TRIM(city) AS cleaned_city
FROM customers;

9.5 Extract the Numeric Portion of a Fixed-Format Code

Suppose customer codes follow the fixed format CUS-1025. The numeric portion can be extracted using a known starting position.

SELECT
    customer_code,
    SUBSTRING(customer_code, 5) AS customer_number
FROM customers;

This works when the prefix is always four characters long, including the hyphen. If the format varies, use a delimiter-based extraction approach or validate the code structure.

10. Using String Functions in WHERE Conditions

String functions can be used in a WHERE clause to filter records according to a transformed value.

10.1 Find Names with Leading or Trailing Spaces

SELECT customer_id, customer_name
FROM customers
WHERE customer_name <> TRIM(customer_name);

This condition identifies rows whose value differs from its default space-trimmed version. It is useful for identifying potential text-cleaning issues.

10.2 Find Codes with a Particular Prefix

SELECT customer_code
FROM customers
WHERE SUBSTRING(customer_code, 1, 3) = 'CUS';

This filters codes whose first three characters are CUS. For a straightforward prefix search, a condition such as customer_code LIKE 'CUS%' may be preferable because it can be more index-friendly in suitable circumstances.

10.3 Find Phone Numbers Containing Hyphens

SELECT customer_id, phone_number
FROM customers
WHERE phone_number LIKE '%-%';

This query locates phone numbers that contain a hyphen. You can then decide whether the values need normalization.

11. Updating Stored Data with Text Functions

Using a string function in a SELECT statement changes only the returned result. If you need to permanently modify stored values, an UPDATE statement is required.

Important: Before updating production data, inspect the affected rows, make a backup where appropriate, and test the change. Use a suitable WHERE condition to avoid modifying unintended records.

11.1 Permanently Remove Edge Spaces

UPDATE customers
SET customer_name = TRIM(customer_name)
WHERE customer_name <> TRIM(customer_name);

This updates names that differ from their trimmed versions. Review the records first if preserving the original text is important.

11.2 Permanently Replace Hyphens in Phone Numbers

UPDATE customers
SET phone_number = REPLACE(phone_number, '-', '')
WHERE phone_number LIKE '%-%';

This removes hyphens from stored phone numbers. Confirm that hyphens are not required for the application's formatting rules before running this update.

11.3 Preview Changes Before Updating

SELECT
    customer_id,
    customer_name AS original_name,
    TRIM(customer_name) AS proposed_name
FROM customers
WHERE customer_name <> TRIM(customer_name);

Previewing original and proposed values is a good practice because it lets you verify the transformation before saving it permanently.

12. Common Errors and Mistakes

13. NULL Values and String Manipulation

In MySQL, NULL represents a missing or unknown value. It is not the same as an empty string or a string containing spaces.

For these three functions, a NULL argument generally results in a NULL output.

SELECT SUBSTRING(NULL, 1, 3) AS result;
SELECT TRIM(NULL) AS result;
SELECT REPLACE(NULL, 'A', 'B') AS result;

Each query returns NULL. If missing text needs a fallback value, handle it explicitly.

SELECT
    TRIM(COALESCE(customer_name, 'Unknown Customer')) AS cleaned_name
FROM customers;

In this example, COALESCE() supplies a fallback string when customer_name is NULL, and TRIM() removes edge spaces from the resulting value.

14. Performance Considerations and Best Practices

• Use the simplest function that meets the requirement: Avoid unnecessary nesting when one operation is sufficient.
• Validate text formats: Fixed-position extraction is reliable only when the source strings follow a consistent format.
• Remember character positions: MySQL starts SUBSTRING() positions at 1.
• Use TRIM() for edge cleanup: It is not designed to remove every occurrence of a character throughout a string.
• Understand REPLACE() behavior: It replaces all matching occurrences and performs a case-sensitive search.
• Be cautious with updates: Test a SELECT expression before using the same transformation in an UPDATE.
• Consider indexes: Applying functions to columns in filter conditions can limit the use of ordinary indexes. Prefer direct predicates when equivalent, or consider an appropriate generated column or functional index if supported by the MySQL version and use case.
• Use suitable character sets: Ensure text columns use a character set appropriate for the languages and characters being stored.
• Test NULL values: Determine how missing input should be handled before combining string functions.
• Avoid destructive cleanup: Removing spaces, punctuation, or prefixes may change meaningful data. Keep original values when traceability is important.

15. Mini Project: Clean Customer Records

Project objective: Use SUBSTRING(), TRIM(), and REPLACE() to prepare customer data for reporting.

15.1 Scenario

A company has imported customer records from different sources. Some names contain extra spaces, phone numbers contain hyphens, and customer codes follow a fixed format. The company needs a cleaned report without immediately changing the original records.

15.2 Sample Table Structure

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    phone_number VARCHAR(20),
    customer_code VARCHAR(30),
    city VARCHAR(50)
);

15.3 Insert Sample Data

INSERT INTO customers
(customer_id, customer_name, phone_number, customer_code, city)
VALUES
(1, '  Amit Sharma  ', '987-654-3210', 'CUS-1001', ' Mumbai '),
(2, 'Priya Patel', '912-345-6780', 'CUS-1002', 'Pune'),
(3, '  Rahul Verma', '998-765-4321', 'CUS-1003', ' Delhi ');

15.4 Generate a Cleaned Report

SELECT
    customer_id,
    TRIM(customer_name) AS cleaned_name,
    REPLACE(phone_number, '-', '') AS cleaned_phone,
    SUBSTRING(customer_code, 5) AS customer_number,
    TRIM(city) AS cleaned_city
FROM customers
ORDER BY customer_id;

15.5 Project Explanation

• TRIM(customer_name) removes spaces at the beginning and end of each name.
• REPLACE(phone_number, '-', '') removes hyphens from the displayed phone number.
• SUBSTRING(customer_code, 5) extracts the part of the code after the fixed CUS- prefix.
• TRIM(city) removes leading and trailing spaces from city names.
• ORDER BY customer_id sorts the report by customer identifier.

The report produces cleaned output while preserving the original stored values. If permanent cleanup is required, validate the proposed changes and then run suitable update statements.

16. Practice Exercises

1. Extract the first four characters from the string DATABASE.
2. Extract three characters from MYSQLTRAINING, starting at position 6.
3. Return the last three characters of COMPUTER using a negative starting position with SUBSTRING().
4. Remove leading and trailing spaces from Learn MySQL .
5. Remove only leading spaces from SQL Course.
6. Remove the prefix EMP- from the beginning of EMP-4501.
7. Replace every hyphen in 111-222-333 with an empty string.
8. Replace every occurrence of cat with dog in cat-cat-cat.
9. Display customer names after applying TRIM() to the customers table.
10. Display phone numbers after removing hyphens from the customers table.
11. Extract the four-digit year from a consistently formatted product code such as PRD-2026-450.
12. Combine TRIM() and REPLACE() to remove edge spaces and change remaining spaces to underscores.

17. Practice Exercise Solutions

Solution 1: Extract the First Four Characters

SELECT SUBSTRING('DATABASE', 1, 4) AS result;
-- Result: DATA

Solution 2: Extract Three Characters from Position 6

SELECT SUBSTRING('MYSQLTRAINING', 6, 3) AS result;
-- Result: RAI

Solution 3: Extract the Last Three Characters

SELECT SUBSTRING('COMPUTER', -3) AS result;
-- Result: TER

Solution 4: Remove Edge Spaces

SELECT TRIM('  Learn MySQL  ') AS result;
-- Result: Learn MySQL

Solution 5: Remove Leading Spaces

SELECT TRIM(LEADING FROM '   SQL Course') AS result;
-- Result: SQL Course

Solution 6: Remove a Prefix

SELECT TRIM(LEADING 'EMP-' FROM 'EMP-4501') AS result;
-- Result: 4501

Solution 7: Remove Hyphens

SELECT REPLACE('111-222-333', '-', '') AS result;
-- Result: 111222333

Solution 8: Replace All Occurrences

SELECT REPLACE('cat-cat-cat', 'cat', 'dog') AS result;
-- Result: dog-dog-dog

Solution 9: Clean Customer Names

SELECT
    customer_id,
    TRIM(customer_name) AS cleaned_name
FROM customers;

Solution 10: Remove Hyphens from Phone Numbers

SELECT
    customer_id,
    REPLACE(phone_number, '-', '') AS cleaned_phone
FROM customers;

Solution 11: Extract the Year

SELECT SUBSTRING('PRD-2026-450', 5, 4) AS product_year;
-- Result: 2026

Solution 12: Combine TRIM() and REPLACE()

SELECT REPLACE(TRIM('  My SQL Training  '), ' ', '_') AS result;
-- Result: My_SQL_Training

18. Interview Questions and Answers

Q1. What is the purpose of SUBSTRING() in MySQL?

SUBSTRING() extracts part of a string based on a starting position and an optional length.

Q2. Does SUBSTRING() start counting positions from zero or one?

MySQL counts the first character as position 1. A starting position of zero returns an empty string.

Q3. Can SUBSTRING() extract characters from the end of a string?

Yes. A negative starting position counts backward from the end of the string.

Q4. What is the purpose of TRIM()?

TRIM() removes specified characters from the beginning, end, or both ends of a string. By default, it removes spaces.

Q5. What is the difference between TRIM(), LTRIM(), and RTRIM()?

TRIM() normally removes spaces from both ends. LTRIM() removes leading spaces, and RTRIM() removes trailing spaces.

Q6. Can TRIM() remove a specified prefix?

Yes. For example, TRIM(LEADING 'EMP-' FROM 'EMP-4501') returns 4501.

Q7. What does REPLACE() do?

REPLACE() searches the input string and replaces all occurrences of a specified substring with another substring.

Q8. Does REPLACE() replace only the first match?

No. It replaces every occurrence of the specified search substring in the input string.

Q9. Is REPLACE() case-sensitive in MySQL?

Yes. Its substring matching is case-sensitive, so uppercase and lowercase versions may produce different results.

Q10. Does TRIM() remove spaces inside a sentence?

No. The default form removes spaces from the beginning and end, not from between words.

Q11. Can these functions be used with table columns?

Yes. They can process values from columns in queries, such as TRIM(customer_name) or SUBSTRING(customer_code, 1, 3).

Q12. Does a SELECT query using REPLACE() update stored data?

No. It changes only the expression returned by the query. Permanent changes require an appropriate data-modification statement, such as UPDATE.

Q13. What happens if the input to these functions is NULL?

These functions generally return NULL when a required argument is NULL.

Q14. Can multiple string functions be combined?

Yes. Functions can be nested. For example, REPLACE(TRIM(' My SQL '), ' ', '-') first removes edge spaces and then replaces the remaining space with a hyphen.

Q15. Why should data be previewed before an UPDATE statement?

Previewing values helps verify that the transformation is correct and prevents unintended changes to important stored data.

19. Quick Revision Checklist

• SUBSTRING(string, position, length): Extracts a section of text.
• SUBSTRING(string, position): Extracts text from a position to the end.
• Position 1: Represents the first character in MySQL.
• Negative position: Counts backward from the end of the string.
• TRIM(string): Removes spaces from both ends.
• TRIM(LEADING ... FROM string): Removes matching characters from the beginning.
• TRIM(TRAILING ... FROM string): Removes matching characters from the end.
• REPLACE(string, search, replacement): Replaces all matching occurrences.
• Case sensitivity: REPLACE() performs case-sensitive matching.
• NULL behavior: A NULL argument generally produces a NULL result for these functions.
• SELECT vs UPDATE: A SELECT expression transforms output; UPDATE can change stored values.
• Best practice: Test text transformations on sample values before using them on important data.

20. Official Documentation and Further Learning

For additional details about supported syntax and behavior, refer to the official MySQL documentation:

• MySQL 8.4 – String Functions and Operators
• MySQL 8.4 – Functions and Operators Reference
• MySQL – Character Sets and Collation of Function Results
• Explore MySQL Training at JustAcademy
• Register for a JustAcademy Course Demo

21. Conclusion

Text manipulation is an important part of working with real-world MySQL databases. The SUBSTRING() function extracts a portion of text, TRIM() removes unwanted characters from the edges of a string, and REPLACE() replaces all occurrences of a specified substring.

These functions can be used individually or combined to clean customer records, standardize phone numbers, extract product codes, prepare reports, and transform imported data. Understanding their syntax, character positions, case sensitivity, NULL behavior, and limitations helps you write accurate SQL queries.

Practice these functions with different text values and database columns to build confidence. For further learning, visit the MySQL Training Course or register for a course demo.